********************************************************************************
*                                                                              *
*                          CREATE WAGE AND FIRM DATA                           *
*                                                                              *
********************************************************************************

// Directory
cd "H:\"

// Options
global spolis = 1
global spolismerge = 1

// Years to include
global yearlist 2010 2011 2012 2013 2014 2015 2016 2017 2018 2019 2020 2021

////////////////////////////////////////////////////////////////////////////////
///////////////////////Create SPOLIS data for each year/////////////////////////
////////////////////////////////////////////////////////////////////////////////

if $spolis == 1 {
	import spss SSOORTBAAN SCONTRACTSOORT SARBEIDSRELATIEFLEX using "K:\Utilities\Code_Listings\SSBreferentiebestanden\ARBEIDSRELATIEFLEXREFV1.SAV", case(lower) clear
	save "Data\Flexcontract_reference.dta", replace
	
	foreach year in $yearlist {
		if `year'==2014 {
			import spss RINPERSOONS RINPERSOON SAANTVERLU SBEID SAUTOVANDEZAAK SARBEIDSRELATIE SCDAARD SLNINGLD SSECT SCAOSECTOR SLNLBPH SSOORTBAAN SCONTRACTSOORT SREGULIEREUREN IKVID SDATUMAANVANGIKV SDATUMEINDEIKV using "G:\Spolis\SPOLISBUS\\`year'\SPOLISBUS `year'V1.sav", case(lower) clear
		}
		if `year'==2010 | `year'==2011 | `year'==2012 | `year'==2017 {
			import spss RINPERSOONS RINPERSOON SAANTVERLU SBEID SAUTOVANDEZAAK SARBEIDSRELATIE SCDAARD SLNINGLD SSECT SCAOSECTOR SLNLBPH SSOORTBAAN SCONTRACTSOORT SREGULIEREUREN IKVID SDATUMAANVANGIKV SDATUMEINDEIKV using "G:\Spolis\SPOLISBUS\\`year'\SPOLISBUS`year'V2.sav", case(lower) clear
		}
		if `year'==2013 | `year'==2016 {
			import spss RINPERSOONS RINPERSOON SAANTVERLU SBEID SAUTOVANDEZAAK SARBEIDSRELATIE SCDAARD SLNINGLD SSECT SCAOSECTOR SLNLBPH SSOORTBAAN SCONTRACTSOORT SREGULIEREUREN IKVID SDATUMAANVANGIKV SDATUMEINDEIKV using "G:\Spolis\SPOLISBUS\\`year'\SPOLISBUS`year'V3.sav", case(lower) clear
		}
		if `year'==2020 {
			import spss RINPERSOONS RINPERSOON SAANTVERLU SBEID SAUTOVANDEZAAK SARBEIDSRELATIE SCDAARD SLNINGLD SSECT SCAOSECTOR SLNLBPH SSOORTBAAN SCONTRACTSOORT SREGULIEREUREN IKVID SDATUMAANVANGIKV SDATUMEINDEIKV using "G:\Spolis\SPOLISBUS\\`year'\SPOLISBUS`year'V5.sav", case(lower) clear
		}
		if `year'==2021 {
			import spss RINPERSOONS RINPERSOON SAANTVERLU SBEID SAUTOVANDEZAAK SARBEIDSRELATIE SCDAARD SLNINGLD SSECT SCAOSECTOR SLNLBPH SSOORTBAAN SCONTRACTSOORT SREGULIEREUREN IKVID SDATUMAANVANGIKV SDATUMEINDEIKV using "G:\Spolis\SPOLISBUS\\`year'\SPOLISBUS`year'V4.sav", case(lower) clear
		}
		
		if `year'==2015  {
			import spss RINPERSOONS RINPERSOON SAANTVERLU SBEID SAUTOVANDEZAAK SARBEIDSRELATIE SCDAARD SLNINGLD SSECT SCAOSECTOR SLNLBPH SSOORTBAAN SCONTRACTSOORT SREGULIEREUREN IKVID SDATUMAANVANGIKV SDATUMEINDEIKV using "G:\Spolis\SPOLISBUS\\`year'\SPOLISBUS `year'V3.sav", case(lower) clear
		}
		if `year'==2018  {
			import spss RINPERSOONS RINPERSOON SAANTVERLU SBEID SAUTOVANDEZAAK SARBEIDSRELATIE SCDAARD SLNINGLD SSECT SCAOSECTOR SLNLBPH SSOORTBAAN SCONTRACTSOORT SREGULIEREUREN IKVID SDATUMAANVANGIKV SDATUMEINDEIKV using "G:\Spolis\SPOLISBUS\\`year'\SPOLISBUS`year'V5.sav", case(lower) clear
		}
		if `year'==2019  {
			import spss RINPERSOONS RINPERSOON SAANTVERLU SBEID SAUTOVANDEZAAK SARBEIDSRELATIE SCDAARD SLNINGLD SSECT SCAOSECTOR SLNLBPH SSOORTBAAN SCONTRACTSOORT SREGULIEREUREN IKVID SDATUMAANVANGIKV SDATUMEINDEIKV using "G:\Spolis\SPOLISBUS\\`year'\SPOLISBUS`year'V6.sav", case(lower) clear
		}

		keep if rinpersoons == "R"
		drop rinpersoons

		rename (saantverlu slningld slnlbph sreguliereuren) (hoursworked income_gross income_beftax contracthours)
	
		if `year' > 2009 {
			merge m:1 ssoortbaan scontractsoort using "Data\Flexcontract_reference.dta", nogen keep(1 3)
			g flexcontract = .
			replace flexcontract = 0 if sarbeidsrelatieflex == "1"
			replace flexcontract = 1 if sarbeidsrelatieflex == "2"
			
		}
				
		g companycar = sautovandezaak=="1"
		drop sarbeidsrelatie sautovandezaak

		destring sbeid, force replace
		destring scaosector, force replace
		destring ssect, force replace
		destring sdatumaanvangikv, force replace
		destring sdatumeindeikv, force replace
		rename (sbeid scaosector ssect) (beid caosector sect)

		
		sort rinpersoon beid ikvid sdatumaanvangikv sdatumeindeikv
		g Δjobchange = rinpersoon==rinpersoon[_n-1] & beid==beid[_n-1] & ikvid!=ikvid[_n-1] & sdatumaanvangikv>=sdatumeindeikv[_n-1]
		sort rinpersoon ikvid beid sdatumaanvangikv sdatumeindeikv
		g Δfirmchange = rinpersoon==rinpersoon[_n-1] & beid!=beid[_n-1] & ikvid!=ikvid[_n-1] & sdatumaanvangikv>=sdatumeindeikv[_n-1]
		
		drop if beid == . | caosector == . | sect == .
		
		g flexcontractXhoursworked = flexcontract*hoursworked
		g companycarXhoursworked = companycar*hoursworked
		
		// Keep ikvid of job WITHIN THE FIRM that generates most hours
		bysort rinpersoon beid: egen maxhoursworked = max(hoursworked)
		replace ikvid = "" if int(maxhoursworked)!=int(hoursworked)
		drop maxhoursworked
		
		collapse (max) caosector sect Δjobchange Δfirmchange (sum) hoursworked contracthours income_gross income_beftax flexcontractXhoursworked companycarXhoursworked (firstnm) ikvid, by(rinpersoon beid)
		collapse (max) caosector sect Δjobchange Δfirmchange (sum) hoursworked contracthours income_gross income_beftax flexcontractXhoursworked companycarXhoursworked (firstnm) beid, by(rinpersoon ikvid)
		
		g flexcontract = flexcontractXhoursworked/hoursworked
		g companycar = companycarXhoursworked/hoursworked
		drop flexcontractXhoursworked companycarXhoursworked
		
		drop flexcontract caosector
		g hwage = income_gross/hoursworked
	foreach pctile in 10 20 30 40 50 60 70 80 90 {
		bysort beid: egen pctile_`pctile' = pctile(hwage), p(`pctile')
		if `pctile' == 10 {
			g hwage_`pctile' = hwage<=pctile_`pctile'
		}
		if `pctile' > 10 {
			local pctile_low = `pctile'-10
			g hwage_`pctile' = hwage<=pctile_`pctile' & hwage>pctile_`pctile_low'
		}
	}
	g hwage_pctile = 100*(hwage_10+hwage_20+hwage_30+hwage_40+hwage_50+hwage_60+hwage_70+ hwage_80+hwage_90 ==0)
	foreach pctile in 10 20 30 40 50 60 70 80 90 {
		replace hwage_pctile = hwage_`pctile'*`pctile' if hwage_pctile==0
		drop hwage_`pctile' pctile_`pctile'
	}
		format beid %13.0f
		
		
		g year = `year'	
		compress
		do "Do-files\Variable labels"
		save "Data\SPolis_`year'.dta", replace
	}
}

////////////////////////////////////////////////////////////////////////////////
//////////////////////Merge SPOLIS spells and remove data///////////////////////
////////////////////////////////////////////////////////////////////////////////

if $spolismerge == 1 {
	clear all

	foreach year in $yearlist {
		use "Data\SPolis_`year'.dta", clear
		merge m:1 beid year using "H:\Robots and Workers\Data\IHG_panel_20102019.dta", nogen keep(1 3) update keepusing(cimp_tot cimp_mach cimp_robots cexp_robots creexp_robots)
					
		if `year'>2010 {
			replace year = year-1 // Merge to investments in previous years
			merge m:1 beid year using "H:\Robots and Workers\Data\IHG_panel_20102019.dta", nogen keep(1 3) update keepusing(cimp_tot cimp_mach cimp_robots cexp_robots creexp_robots)
			merge m:1 beid year using "H:\Robots and Workers\Data\Investments.dta", nogen keep(1 3 4 5) update keepusing(cinv_total cinv_computers cinv_mach cinv_softw cinv_specsoftw)
			foreach var in cimp_tot cimp_mach cimp_robots cexp_robots creexp_robots cinv_total cinv_computers cinv_mach cinv_softw cinv_specsoftw {
				rename `var' `var'_t1
			}	

			replace year = year+1
		}
		if `year'>2011 {
			replace year = year-2 // Merge to investments in previous years
			merge m:1 beid year using "H:\Robots and Workers\Data\IHG_panel_20102019.dta", nogen keep(1 3) update keepusing(cimp_robots)
			rename (cimp_robots) (cimp_robots_t2)
			replace year = year+2
		}
		if `year'>2012 {
			replace year = year-3 // Merge to investments in previous years
			merge m:1 beid year using "H:\Robots and Workers\Data\IHG_panel_20102019.dta", nogen keep(1 3) update keepusing(cimp_robots)
			rename (cimp_robots) (cimp_robots_t3)
			replace year = year+3
		}
		if `year'>2013 {
			replace year = year-4 // Merge to investments in previous years
			merge m:1 beid year using "H:\Robots and Workers\Data\IHG_panel_20102019.dta", nogen keep(1 3) update keepusing(cimp_robots)
			rename (cimp_robots) (cimp_robots_t4)
			replace year = year+4
		}
		if `year'>2014 {
			replace year = year-5 // Merge to investments in previous years
			merge m:1 beid year using "H:\Robots and Workers\Data\IHG_panel_20102019.dta", nogen keep(1 3) update keepusing(cimp_robots)
			rename (cimp_robots) (cimp_robots_t5)
			replace year = year+5
		}	
		
		save "Data\SPolis_`year'_matched.dta", replace
	}


	clear all
	foreach year in $yearlist {
		append using "Data\SPolis_`year'_matched.dta"
		capture drop cinv_total_t cinv_computers_t cinv_mach_t cinv_softw_t cinv_specsoftw_t cimp_tot_t1 cimp_mach_t1 cimp_robots_t1 cexp_robots_t1 creexp_robots_t1 cinv_total_t1 cinv_computers_t1 cinv_mach_t1 cinv_softw_t1 cinv_specsoftw_t1 income_beftax
		erase "Data\SPolis_`year'_matched.dta"
	}
	
	
	drop if hoursworked>4380 | hoursworked<=0 //More than 12 hours a day throughout the year.
	drop if income_gross <= 0 | income_gross > 1000000
	
	g loghoursworked = ln(hoursworked)
	order loghoursworked, after(hoursworked)
	g logincome_gross = ln(income_gross)
	order logincome_gross, after(income_gross)
	g hwage = (income_gross/hoursworked)
	replace hwage = . if hwage < 1.5 | hwage > 250
	g loghwage = ln(hwage)
	order *hwage, after(logincome_gross)
	
	g robots = cimp_robots >0 & cimp_robots!=.
	g robotsfirm = cimp_robots>0 & cimp_robots!=.
	bysort beid: ereplace robotsfirm = max(robotsfirm)
	
	g firm_workers = 1
	bysort beid year: ereplace firm_workers = total(firm_workers)
	bysort beid year: egen firm_hoursworked = total(hoursworked)
	bysort beid year: egen firm_income = total(income_gross)	
	bysort beid year: egen firm_hwage = total(hwage*hoursworked)
	replace firm_hwage = firm_hwage/firm_hoursworked
	replace hwage = . if hwage < 1.5 | hwage > 250
	g logfirm_workers = ln(firm_workers)
	order logfirm_workers, after(firm_workers)
	g logfirm_hoursworked = ln(firm_hoursworked)
	order logfirm_hoursworked, after(firm_hoursworked)
	g logfirm_hwage = ln(firm_hwage)
	order logfirm_hwage, after(firm_hwage)
	g logfirm_income = ln(firm_income)
	order logfirm_income, after(firm_income)	
	
	do "Do-files\Variable labels"
	
	compress
	save "H:\Robots and Workers\Data\SPOLIS_merge.dta", replace	
	
	// Create (almost) balanced panel of workers in 2010 and 2019
	import spss RINPERSOON GBAGEBOORTELAND GBAGESLACHT GBAGEBOORTELANDMOEDER GBAGEBOORTELANDVADER GBAGEBOORTEJAAR using "G:\Bevolking\GBAPERSOONTAB\2010\GBAPERSOON2010TABV1.sav", case(lower) clear
	g male = gbageslacht=="1"
	destring gbageboortejaar, force replace
	g age = 2010-gbageboortejaar
	g migrant = gbageboorteland!="6030"
	g migrant_2ndgen = gbageboortelandmoeder!="6030" | gbageboortelandvader!="6030"
	keep rinpersoon age migrant migrant_2ndgen male
	g year = 2010
	duplicates drop rinpersoon, force
	save "H:\Robots and Workers\Data\SPOLIS_temppeople2010.dta", replace
	
	import spss RINPERSOON GBAGEBOORTELAND GBAGESLACHT GBAGEBOORTELANDMOEDER GBAGEBOORTELANDVADER GBAGEBOORTEJAAR using "G:\Bevolking\GBAPERSOONTAB\2019\GBAPERSOON2019TABV1.sav", case(lower) clear
	g male = gbageslacht=="1"
	destring gbageboortejaar, force replace
	g age = 2019-gbageboortejaar
	g migrant = gbageboorteland!="6030"
	g migrant_2ndgen = gbageboortelandmoeder!="6030" | gbageboortelandvader!="6030"
	keep rinpersoon age migrant migrant_2ndgen male
	g year = 2019
	duplicates drop rinpersoon, force
	save "H:\Robots and Workers\Data\SPOLIS_temppeople2019.dta", replace
		
	use "H:\Robots and Workers\Data\SPOLIS_merge.dta", clear
	keep if year == 2010 | year == 2019
	drop if sect == 0
	capture drop *_t1 *_t2 *_t3 *_t4 *_t5
	
	preserve 
		keep beid year robots robotsfirm cimp_robots firm_*
		rename (firm_workers firm_hoursworked firm_income firm_hwage) (fworkers fhoursworked fincome fhwage)
		duplicates drop beid year, force
		save "H:\Robots and Workers\Data\SPOLIS_tempfirms.dta", replace
		keep if year == 2010
		keep beid fworkers fhoursworked fincome fhwage
		rename (fworkers fhoursworked fincome fhwage) (fworkers10 fhoursworked10 fincome10 fhwage10)
		save "H:\Robots and Workers\Data\SPOLIS_tempfirms2010.dta", replace
		use "H:\Robots and Workers\Data\SPOLIS_tempfirms.dta"
		keep if year == 2019
		keep beid fworkers fhoursworked fincome fhwage
		rename (fworkers fhoursworked fincome fhwage) (fworkers19 fhoursworked19 fincome19 fhwage19)
		save "H:\Robots and Workers\Data\SPOLIS_tempfirms2019.dta", replace
	restore

	keep rinpersoon year beid sect hoursworked hwage companycar flexcontract income_gross

	sort rinpersoon beid year

	global N = _N
	global M = _N*2
	set obs $M

	qui forvalues i = 1(1)$N {
		local n = $N + `i'
			
		replace rinpersoon = rinpersoon[`i'] in `n' if year[`i']==2010 & beid[`i']!=beid[`i'+1]
		replace beid = beid[`i'] in `n' if year[`i']==2010 & beid[`i']!=beid[`i'+1]
		replace sect = sect[`i'] in `n' if year[`i']==2010 & beid!=beid[`i'+1]
		replace year = 2019 in `n' if year[`i']==2010 & beid!=beid[`i'+1]
		foreach var in hoursworked hwage income_gross flexcontract companycar {
			replace `var' = 0 in `n' if year[`i']==2010 & beid!=beid[`i'+1]
		}
		
		replace rinpersoon = rinpersoon[`i'] in `n' if year[`i']==2019 & beid[`i']!=beid[`i'-1]
		replace beid = beid[`i'] in `n' if year[`i']==2019 & beid[`i']!=beid[`i'-1]
		replace sect = sect[`i'] in `n' if year[`i']==2019 & beid[`i']!=beid[`i'-1]
		replace year = 2010 in `n' if year[`i']==2019 & beid[`i']!=beid[`i'-1]
		foreach var in hoursworked hwage income flexcontract companycar {
			replace `var' = 0 in `n' if year[`i']==2019 & beid[`i']!=beid[`i'-1]
		}	
		
	}

	drop if rinpersoon == ""
	merge m:1 beid year using "H:\Robots and Workers\Data\SPOLIS_tempfirms.dta", nogen keep(1 3)
	merge m:1 beid using "H:\Robots and Workers\Data\SPOLIS_tempfirms2010.dta", nogen keep(1 3)
	merge m:1 beid using "H:\Robots and Workers\Data\SPOLIS_tempfirms2019.dta", nogen keep(1 3)
	merge m:1 rinpersoon year using "H:\Robots and Workers\Data\SPOLIS_temppeople2010.dta", nogen keep(1 3 4 5) update
	merge m:1 rinpersoon year using "H:\Robots and Workers\Data\SPOLIS_temppeople2019.dta", nogen keep(1 3 4 5) update
	
	drop if age < 18 | age > 67
	g age1825 = age >= 18 & age <=25
	g age2635 = age >= 26 & age <=35
	g age3645 = age >= 36 & age <=45
	g age4655 = age >= 46 & age <=55
	g age5667 = age >= 56 & age <=67
	order migrant migrant_2ndgen, after(age5667)
	
	drop if fhoursworked == . | fhoursworked10==. | fhoursworked19==.
	drop fworkers19 fhoursworked19 fincome19 fhwage19
	
	g logfworkers10Xyear = ln(fworkers10)*year
	g logfhoursworked10Xyear = ln(fhoursworked10)*year
	g logfhwage10Xyear = ln(fhwage10)*year
	g logfincome10Xyear = ln(fincome10)*year
	
	erase "H:\Robots and Workers\Data\SPOLIS_tempfirms.dta"
	erase "H:\Robots and Workers\Data\SPOLIS_tempfirms2010.dta"
	erase "H:\Robots and Workers\Data\SPOLIS_temppeople2010.dta"
	erase "H:\Robots and Workers\Data\SPOLIS_temppeople2019.dta"
	

	
	sort rinpersoon beid year

	save "H:\Robots and Workers\Data\SPOLIS_balancedpanel.dta", replace
	
	
}
